<?

//Data loading
$title='DETAIL UMUR HUTANG';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

$page   = 'PAGE';

$DEFINE_FONT=0;
$Result = GridList ();
if ($Result) {
	foreach ($Result as $Row) {
		foreach ($Row as $Field) {
			if (strlen($strExcelRow)) $strExcelRow .= ";$Field";
			else $strExcelRow = "$Field";
		}
		$strOutput .= $strExcelRow."\n";
		$strExcelRow="";
	}
	header("Content-type:application/vnd.notepad");
	header("Content-Disposition:filename=ais_aging_hutang_stmt".".csv");
	print $strOutput;
}

function GetFromDate () {
 	list($Day,$Month,$Year,$NMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,GetParam("m",""),1,GetParam("y",""))));
 	return array($Day,$Month,$Year,$NMonth);
}

function GetLastPeriod ($Month, $Year) {
	list($LDay,$LMonth,$LYear,$LNMonth) = explode("-",date("d-m-Y-F",mktime(0,0,0,$Month,0,$Year)));
	return array($LMonth,$LYear,$LNMonth);
}

function FlushCustomer ($TableName, $tmp) {
	global $DBConnection;

	if ($tmp) $TEMPORARY = "TEMPORARY";
       
	//$SQL = "DROP TABLE IF EXISTS `$TableName`;";
	//$DBConnection->dbc->query($SQL);

	$SQL= "CREATE $TEMPORARY TABLE `$TableName` (
			  `cucode` varchar(20) NOT NULL default '',
			  `cunama` varchar(50) default NULL,
			  `top` int(4) default NULL

			  )";
	$DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);
        

	return true;
}

function GridList () {
		 	global $DBConnection;
			//$TEMP = "TEMPORARY";

		 	FlushCustomer ("aiscustomer", 1);
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));
			
			list($day,$month,$year) = explode("/",GetParam("agingDate",""));
			$current_date = "$year-$month-$day";

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terhutang`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                                          `nobkta` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `jumlah` varchar(32) default NULL,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

						
			$SQL = "
					INSERT INTO ais_hutang_terhutang
					SELECT ais_hutang.KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, sum(JUMLAH), TOP, 
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))
					FROM ais_hutang
					LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='1' AND ais_hutang.tglbkt<='$current_date' ";
                                        
                    if (GetParam("office",""))   $SQL .= " AND ais_hutang.ktrasl = '".GetParam("office","")."' ";
                    if (GetParam("curr",""))   $SQL .= " AND ais_hutang.curr = '".GetParam("curr","")."' ";
                    if (GetParam("vendor","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("vendor","")."' ";
                    $SQL .= " GROUP BY  nobkt,skada  ";
                    $SQL .= " order by nobkt";

                        //print $SQL;die();

			$DBConnection->dbc->query($SQL);

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terbayar`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_hutang_terbayar
					SELECT NOBKT, NOBKTA, SUM(JUMLAH) 
					FROM ais_hutang
                    LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='2' AND ais_hutang.tglbkt<='$current_date' ";
                        if (GetParam("office",""))   $SQL .= " AND ais_hutang.ktrasl = '".GetParam("office","")."' ";
                        if (GetParam("curr",""))   $SQL .= " AND ais_hutang.curr = '".GetParam("curr","")."' ";                        
                        if (GetParam("vendor","")) $SQL .=" AND tbldebtur.cucode = '".GetParam("vendor","")."' ";
                                        
                        $SQL .= " group by nobkta order by nobkta";
                        //print $SQL;die();
			$DBConnection->dbc->query($SQL);


			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_remain`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_remain` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
                      `cr_term` tinyint(4)default NULL,
					  `jumlahcurr` varchar(32)default NULL,
					  `jumlah30` varchar(32)default NULL,
					  `jumlah60` varchar(32)default NULL,
					  `jumlah60g` varchar(32)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);


                        $SQL = "
                                INSERT INTO ais_hutang_remain
                                SELECT 
                                ais_hutang_terhutang.ktrasl,
                                ais_hutang_terhutang.tglbkt,
                                ais_hutang_terhutang.nobkt,
                                ais_hutang_terhutang.debtur,
                                ais_hutang_terhutang.debnam,
                                ais_hutang_terhutang.uraian,
                                ais_hutang_terhutang.curr,
                                ifnull(ais_hutang_terhutang.cr_term, 0) AS top,
                                if(ais_hutang_terhutang.aging='0', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                if(ais_hutang_terhutang.aging='30', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah30,
                                if(ais_hutang_terhutang.aging='60', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah60,
                                if(ais_hutang_terhutang.aging='60g', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah90
                                FROM
                                ais_hutang_terhutang
                                LEFT JOIN ais_hutang_terbayar ON (ais_hutang_terhutang.nobkt = ais_hutang_terbayar.nobkta)
                                WHERE
                                  (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR (ais_hutang_terhutang.jumlah-ais_hutang_terbayar.bayar) <> 0
                                ORDER BY debtur,nobkt
                        ";
			$DBConnection->dbc->query($SQL);

                        $SQL = "
					SELECT ktrasl, nobkt, tglbkt, debtur, debnam, uraian, cr_term as top, 
					jumlahcurr, jumlah30, jumlah60, jumlah60g
					FROM ais_hutang_remain 
					ORDER BY debtur
			       ";
			return $DBConnection->dbc->get_results($SQL,ARRAY_A);

}


class PDFReport extends PDF {

	function Header() {
		global $title,$perush,$page,$DEFINE_FONT;
		if ($DEFINE_FONT==0) {
			$this->AddFont('Arial','','arialn.php');
			$DEFINE_FONT=1;
		}

		list($Day,$Month,$Year,$NMonth) = GetFromDate();
		list($LMonth,$LYear,$LNMonth) = GetLastPeriod ($Month, $Year);

          $this->SetFont('Arial','',10);
          $this->Cell(260,5,$perush,0,1,'C',0);
          $this->Cell(260,5,$title,0,1,'C',0);
          $this->Cell(260,5,"AS OF: ".GetParam("agingDate",""),0,1,'C',0);
          $this->Cell(260,5,$page." ".$this->PageNo(),0,1,'R',0);
          $this->Line(10,30,270,30);
          $this->Ln();
          $this->Ln();
	    //Header

	    //Column widths
	    $w=array(80,25,10,25,30,30,30,30);
	    $this->SetFont('Arial','',8);
	    $this->Cell($w[0],5,"DEBTUR",1,0,'C',0);
	    $this->Cell($w[1],5,"NOBKT",1,0,'C',0);
	    $this->Cell($w[2],5,"CR",1,0,'C',0);
        $this->Cell($w[3],5,"CURRENT",1,0,'C',0);
        $this->Cell($w[4],5,"1-30",1,0,'C',0);
        $this->Cell($w[5],5,"31-60",1,0,'C',0);		
        $this->Cell($w[6],5,">60",1,0,'C',0);
        $this->Cell($w[7],5,"TOTAL",1,0,'C',0);
	    $this->Ln();
	}

	function GridTable($MyResult) {
	    //Column widths
	    $w=array(80,25,10,25,30,30,30,30);

	    //Data
	    foreach($MyResult as $row)
	    {
		    $row_total  = $row["jumlahcurr"]+$row["jumlah30"]+$row["jumlah60"]+$row["jumlah60g"];
		    $customer   = $row["debtur"].' - '.$row["debnam"];

	        if (($lastdebnam<>$row["debnam"]) && (strlen($lastdebnam)>0)) {
		    	$this->Cell($w[0]+$w[1]+$w[2],5,"SUB TOTAL:",1,0,'R',0);
				$this->Cell($w[3],5,number_format($subtotalcurr,2),'LRBT',0,'R');
		    	$this->Cell($w[4],5,number_format($subtotal30,2),'LRBT',0,'R');
				$this->Cell($w[5],5,number_format($subtotal60,2),'LRBT',0,'R');
				$this->Cell($w[6],5,number_format($subtotal60g,2),'LRBT',0,'R');
				$this->Cell($w[7],5,number_format($subtotalall,2),'LRBT',0,'R');
		    	$this->Ln();
	        	$subtotalcurr = 0;
	        	$subtotal30   = 0;
	        	$subtotal60   = 0;
	        	$subtotal60g  = 0;
	        	$subtotalall  = 0;	
        	} 
        	
	        $subtotalcurr = $subtotalcurr  + $row["jumlahcurr"];
	        $subtotal30   = $subtotal30    + $row["jumlah30"];
	        $subtotal60   = $subtotal60    + $row["jumlah60"];
	        $subtotal60g  = $subtotal60g   + $row["jumlah60g"];
	        $subtotalall  = $subtotalcurr + $subtotal30 + $subtotal60 + $subtotal60g;

			if (($lastdebnam<>$row["debnam"]) && (strlen($row["debnam"]))) $this->Cell($w[0],5,$customer,'LR');
                        else $this->Cell($w[0],5,"",'LR');

			$this->Cell($w[1],5,$row["nobkt"],'LR');
                        $this->Cell($w[2],5,$row["top"],'LR',0,'R');
			$this->Cell($w[3],5,number_format($row["jumlahcurr"],2),'LR',0,'R');
                        $this->Cell($w[4],5,number_format($row["jumlah30"],2),'LR',0,'R');
			$this->Cell($w[5],5,number_format($row["jumlah60"],2),'LR',0,'R');
			$this->Cell($w[6],5,number_format($row["jumlah60g"],2),'LR',0,'R');
			$this->Cell($w[7],5,number_format($row_total,2),'LR',0,'R');
                        $this->Ln();
                        $lastdebnam = $row["debnam"];

                        $total_cur  = $total_cur + $row["jumlahcurr"];
                        $total_30   = $total_30 + $row["jumlah30"];
                        $total_60   = $total_60 + $row["jumlah60"];
                        $total_60g  = $total_60g + $row["jumlah60g"];

	    }

                        $this->Cell($w[0]+$w[1]+$w[2],5,"SUB TOTAL:",1,0,'R',0);
			$this->Cell($w[3],5,number_format($subtotalcurr,2),'LRBT',0,'R');
                        $this->Cell($w[4],5,number_format($subtotal30,2),'LRBT',0,'R');
			$this->Cell($w[5],5,number_format($subtotal60,2),'LRBT',0,'R');
			$this->Cell($w[6],5,number_format($subtotal60g,2),'LRBT',0,'R');
			$this->Cell($w[7],5,number_format($subtotalall,2),'LRBT',0,'R');
                        $this->Ln();
	        $subtotalcurr = 0;
	        $subtotal30   = 0;
	        $subtotal60   = 0;
	        $subtotal60g  = 0;
	        $subtotalall  = 0;	


	    $total_row = $total_cur + $total_30 + $total_60 + $total_60g;
	    $this->Cell($w[0]+$w[1]+$w[2],5,"TOTAL:",1,0,'R',0);
	    $this->Cell($w[3],5,number_format($total_cur,2),1,0,'R',0);
	    $this->Cell($w[4],5,number_format($total_30,2),1,0,'R',0);
	    $this->Cell($w[5],5,number_format($total_60,2),1,0,'R',0);
            $this->Cell($w[6],5,number_format($total_60g,2),1,0,'R',0);
            $this->Cell($w[7],5,number_format($total_row,2),1,0,'R',0);
	    $this->Ln();

	    //Closure line
	}

	function Footer() {
	    //Closure line
	    $this->Cell(260,0,'','T');
	}

}

?>